-- @owner: lvlintao666
-- @date: 2022/04/12
-- @testpoint: 数组类型在触发器中的使用，update语句触发
-- @modified: by @lihongji 2025/3/13 研发代码变更

--step1:建表;expect:成功
drop table if exists t_ustore_datatype_array_0099_1;
create table t_ustore_datatype_array_0099_1(
  c_id  int primary key,
  c_name varchar2(200)[],
  c_age int[],
  c_sex char(110)[],
  c_address  number[],
  c_email date[],
  c_phonenum real[],
  c_remarks interval day(4) to second[6]
) with (storage_type=ustore);
insert into t_ustore_datatype_array_0099_1 values(1,array['xiaoming','23'],array[1234567.67,12345.4567],array['m','huanpu','@123','1399','none'],array[58814,546223077,1234567.67],array['2012-10-31 10:16:52','2002-08-11','2000-03-01 15:42:21'],array[1],array[(interval '4 5:12:10.222' day to second(3)),(interval '12 15:12:10.222' day to second(6))]);

drop table if exists t_ustore_datatype_array_0099_2;
create table t_ustore_datatype_array_0099_2(c_column  varchar2(15),c_type  varchar2(15)) with (storage_type=ustore);
select * from t_ustore_datatype_array_0099_1;
select * from t_ustore_datatype_array_0099_2;

--step2:创建触发器;expect:成功
create or replace function  tri_insert_func_0099() returns trigger as
$$
declare
begin
    insert into t_ustore_datatype_array_0099_2 values('t_name','update');
    return new;
end
$$ language plpgsql;
/
create  trigger trigger_array_0099 after insert on t_ustore_datatype_array_0099_1
for each row execute procedure tri_insert_func_0099();
/
--step3:更新数据;expect:成功
update t_ustore_datatype_array_0099_1 set c_name=array['xiaoming','xiaohong'] where c_id=1;
select * from t_ustore_datatype_array_0099_1;
select * from t_ustore_datatype_array_0099_2;

--step4:环境清理;expect:成功
drop trigger trigger_array_0099 on t_ustore_datatype_array_0099_1 cascade ;
drop function tri_insert_func_0099() cascade;
drop table t_ustore_datatype_array_0099_1;
drop table t_ustore_datatype_array_0099_2;
